package com.erlinyou.im.dao.repository.ofroster;

import java.util.List;
import java.util.Map;


import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import com.erlinyou.base.dao.BaseRepository;
import com.erlinyou.im.model.ofroster.Ofroster;

public interface OfrosterRepository extends BaseRepository<Ofroster, Long>{
	
	List<Ofroster> findByUsernameAndSub(String userId,byte sub);
	
	@Query(value="SELECT * from ofroster where username = ?1 and sub = 3",nativeQuery=true)
	List<Ofroster> existsByCfrom(String username);

	@Query(value="SELECT * from ofroster where username = ?1 AND substring_index(jid,'@',1) = ?2 and sub = 3",nativeQuery=true)
	public Ofroster findByUsernameAndJid(String username,String jid);
	
	@Query(value="SELECT * from ofroster where username = ?1 AND substring_index(jid,'@',1) = ?2",nativeQuery=true)
	public Ofroster findByUsernameAndJidNotSub(String username,String jid);

	@Modifying
	@Query(value="delete from ofroster where username = ?1 AND substring_index(jid,'@',1) = ?2 and sub = 3",nativeQuery=true)
	int deleteByUsernameAndJid(Long userId, long rid);
	
	
	@Query(value="SELECT substring_index(jid,'@',1) as jid from ofroster where username = ?1 AND sub=3 AND FIND_IN_SET(substring_index(jid,'@',1),?2)>0",nativeQuery=true)
	List<Map<String, String>> findByUsername(Long username,String ids);

	List<Ofroster> findBySub(byte sub);
	
}
